Importing and Exporting CSV Files in MySQL

Course: MySQL Training

Topic: Importing and Exporting CSV Files

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to CSV Files in MySQL

CSV stands for Comma-Separated Values. It is a simple text-based file format used to store structured information in rows and columns. Each line generally represents a record, and commas separate the individual values.

MySQL supports importing data from CSV files and exporting database records into CSV-compatible files. These operations help transfer data between MySQL, spreadsheets, business applications, reporting systems, and other databases.

Importing means loading records from an external CSV file into a MySQL table. Exporting means extracting selected database records and saving them to a file for external use.

1.1 Example of a CSV File

id,name,department,salary
1,Aarav,IT,45000
2,Meera,HR,40000
3,Rohan,Finance,52000

The first line contains column headings. The remaining lines contain employee records. This file could be used to populate an employee table in MySQL.

1.2 Advantages of CSV Files

• Easy to create, read, and edit.
• Compatible with spreadsheet applications and many programming languages.
• Useful for exchanging data between different systems.
• Suitable for bulk data transfer.
• Stores data without requiring a specialized spreadsheet application.
• Can be generated and processed using scripts.

2. Learning Objectives

After completing this topic, learners will be able to:

• Understand CSV structure, headers, delimiters, and quotation rules.
• Create MySQL databases and tables for imported data.
• Import CSV records using LOAD DATA INFILE.
• Understand the differences between INFILE and LOCAL INFILE.
• Export MySQL records using SELECT ... INTO OUTFILE.
• Import and export data using MySQL Workbench.
• Validate imported records and identify common errors.
• Handle duplicates, null values, data types, and character encoding.
• Apply secure and reliable data-transfer practices.

3. Understanding CSV Structure

3.1 Rows and Columns

A CSV file organizes information into records and fields. Each record generally occupies one line, while fields represent individual values.

3.2 Quoted Fields

A field containing a comma may be enclosed in double quotation marks.

id,name,department
1,"Sharma, Anika",Sales
2,"Patel, Jay",IT

The commas inside the quoted names are part of the values and should not be treated as field separators.

3.3 CSV and Excel Comparison

4. Preparing the MySQL Database

Before importing a CSV file, create a database and a table whose columns are appropriate for the incoming records.

4.1 Create a Database

CREATE DATABASE company_db;
USE company_db;

4.2 Create an Employee Table

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(100),
    salary DECIMAL(10,2)
);

The id column uniquely identifies each employee. The name column stores text, while salary stores numeric values with two decimal places.

4.3 Verify the Table Structure

DESCRIBE employees;

This statement displays the column names, data types, nullability, and key information. Check the structure before importing external data.

5. Preparing the CSV File

Create a file named employees.csv containing the following sample records.

id,name,department,salary
4,Ananya,Marketing,48000.00
5,Kabir,IT,55000.00
6,Isha,HR,42000.00

5.1 Preparation Checklist

• Confirm that each record has the expected number of fields.
• Ensure the header matches the intended column mapping.
• Check that numeric fields contain valid numbers.
• Remove unintended blank rows.
• Confirm the field delimiter and line endings.
• Use a compatible character encoding, such as UTF-8, when appropriate.
• Keep a copy of the original file before cleaning or modifying it.

6. Importing CSV Files Using LOAD DATA INFILE

LOAD DATA INFILE is a MySQL statement designed to load records from a text file into a table efficiently. It is especially useful for bulk imports.

6.1 Syntax

LOAD DATA INFILE 'file_path'
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

6.2 Practical Example

LOAD DATA INFILE '/var/lib/mysql-files/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, department, salary);

Replace the sample path with the actual file location accessible to the MySQL server. The line-ending option must match the file. For Windows-style CRLF files, LINES TERMINATED BY '\r\n' may be appropriate.

6.3 Explanation of Important Clauses

6.4 Verify the Imported Data

SELECT * FROM employees;

SELECT COUNT(*) AS total_records
FROM employees;

Review the records and compare the count with the number of data rows in the source file. Exclude the header when calculating the expected count.

7. Importing CSV Files Using LOAD DATA LOCAL INFILE

LOAD DATA LOCAL INFILE reads a file from the client computer and sends its contents to the MySQL server. It differs from the standard LOAD DATA INFILE statement, which reads a server-accessible file.

7.1 Example

LOAD DATA LOCAL INFILE 'C:/data/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id, name, department, salary);

This example assumes the CSV file is located on a Windows client and uses CRLF line endings. Adjust the file path and line terminator to match the actual environment.

7.2 Differences Between INFILE and LOCAL INFILE

LOCAL loading may be disabled by configuration. Enable it only when necessary, with trusted connections and appropriate security controls.

8. Importing Data with Column Mapping

The order of columns in a CSV file may differ from the order in the MySQL table. Specify the incoming field order explicitly to avoid incorrect mapping.

8.1 Example

Suppose the CSV file contains department, name, ID, and salary in that order.

LOAD DATA LOCAL INFILE 'C:/data/employees_reordered.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(department, name, id, salary);

The column list must follow the exact order of fields in the CSV file. Confirm the number and meaning of fields before executing the import.

8.2 Importing an Unneeded Field

User variables can capture fields that do not need to be inserted directly into the destination table.

LOAD DATA LOCAL INFILE 'C:/data/employees_extra.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id, name, department, salary, @temporary_code);

This example assumes that each source row contains five fields and the final field is intentionally ignored. For complex imports, user variables and a SET clause can be used to transform incoming values.

9. Importing CSV Files Using MySQL Workbench

MySQL Workbench provides a graphical interface for database administration and data import. It can be convenient when users prefer a visual workflow.

9.1 General Steps

1. Open MySQL Workbench and connect to the intended MySQL server.
2. Select the target database.
3. Open the table data import wizard or the import option available in your Workbench version.
4. Select the CSV file from your computer.
5. Choose an existing destination table or configure a new one if supported by the wizard.
6. Review delimiters, quotation settings, header handling, and column mapping.
7. Preview the records to check for malformed rows or unexpected characters.
8. Start the import and review any warnings or errors.
9. Execute a verification query against the destination table.

9.2 Advantages of Workbench

• Provides a visual preview of the file.
• Helps users inspect and map columns.
• Reduces the need to write import SQL manually.
• Is suitable for many one-time data-loading tasks.

9.3 Limitations

• Available controls can differ between versions.
• Large imports may require additional planning or configuration.
• Recurring imports are often better handled through scripts or scheduled workflows.
• Data quality and permissions must still be checked.

10. Exporting MySQL Data Using SELECT INTO OUTFILE

The SELECT ... INTO OUTFILE statement writes selected query results to a file on the MySQL server host. Field and line options can be used to create CSV-compatible output. Refer to the official MySQL SELECT INTO documentation.

10.1 Example

SELECT id, name, department, salary
FROM employees
INTO OUTFILE '/var/lib/mysql-files/employees_export.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

The server writes the file to the specified location. The path is illustrative and must be valid for your MySQL server environment.

10.2 Important Rules

• The output file is created on the MySQL server host, not automatically on the local computer.
• The account requires the appropriate FILE privilege.
• The output file must not already exist.
• The secure_file_priv setting may restrict the permitted directory.
• The MySQL server process needs operating-system permission to write to the destination.
• The statement does not automatically include column headings.

10.3 Check the Server File Directory Setting

SHOW VARIABLES LIKE 'secure_file_priv';

Check the result before choosing an export path. Follow your administrator's security policies when performing file operations.

11. Exporting Selected Records with WHERE

The WHERE clause allows you to export only records that meet specific conditions.

SELECT id, name, department, salary
FROM employees
WHERE department = 'IT'
INTO OUTFILE '/var/lib/mysql-files/it_employees.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

This query exports employees belonging to the IT department. Use a new filename each time when the previous output file still exists.

12. Exporting Selected Columns and Sorted Data

You can choose specific columns and sort the results before exporting them.

SELECT name, department, salary
FROM employees
ORDER BY department, name
INTO OUTFILE '/var/lib/mysql-files/employee_report.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

This statement exports only the name, department, and salary columns, sorted by department and then name.

13. Exporting CSV Files with Headers

The INTO OUTFILE statement exports the selected data rows but does not automatically generate a column-header row. If the destination application expects headers, use a suitable export tool or explicitly generate a header row.

13.1 Example of a Header-Row Query

(SELECT 'id', 'name', 'department', 'salary')
UNION ALL
(SELECT
    CAST(id AS CHAR),
    name,
    department,
    CAST(salary AS CHAR)
 FROM employees)
INTO OUTFILE '/var/lib/mysql-files/employees_with_header.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

The first query supplies the headings, and the second supplies the employee records. For production exports, ensure consistent data types, null handling, character encoding, escaping, and row ordering. For complex CSV data, a CSV-aware library is often preferable.

13.2 Alternative Methods

• Use a MySQL Workbench result-grid export option that supports CSV and headers.
• Fetch query results using a client application and write them with a CSV library.
• Use MySQL Shell export utilities where appropriate for your workflow.

14. Exporting Data Using MySQL Workbench

Workbench can export query results through its graphical interface. This is often convenient for reports and one-time exports to the local computer.

14.1 General Steps

1. Connect to the MySQL server.
2. Run a query, such as SELECT * FROM employees;.
3. Review the result grid to verify the selected records.
4. Choose the result-grid export option if available.
5. Select CSV as the file format.
6. Choose a destination on your computer and save the file.
7. Open the exported file and verify its contents.

The exact controls may vary by Workbench version. Confirm whether the selected export option includes column headings.

15. Handling Delimiters and Special Characters

CSV files may contain commas, quotation marks, and line breaks within individual fields. These characters must be handled correctly to preserve the original values.

15.1 Commas Inside a Field

id,name,department
7,"Kavya, Shah",Sales

The quotation marks indicate that the comma inside the name is part of the field value.

15.2 Quotation Marks Inside a Field

id,name,department
8,"Amit ""AJ"" Kumar",IT

In a commonly used CSV convention, an embedded double quotation mark is represented by two double quotation marks inside a quoted field.

15.3 Best Practices

• Use consistent delimiters and quoting conventions.
• Inspect the original file before importing.
• Use OPTIONALLY ENCLOSED BY '"' when fields may be quoted but are not always quoted.
• Do not assume every file ending in .csv follows the same format.
• Use a CSV-aware parser or writer for fields containing complex text.

16. Handling NULL and Empty Values

An empty field, an empty string, and SQL NULL are different concepts. Their treatment depends on the file representation and import settings.

16.1 Example

id,name,department,salary
9,Dev,IT,46000
10,Nisha,,43000

The second record contains an empty department field. Decide whether it should become an empty string, SQL NULL, or a default value before importing.

16.2 Find NULL Values

SELECT *
FROM employees
WHERE department IS NULL;

16.3 Find Empty Strings

SELECT *
FROM employees
WHERE department = '';

These queries check different conditions. Validate missing-value rules before importing business-critical data.

17. Character Encoding and International Text

CSV files can contain international names, addresses, and symbols. If the file encoding and database character-set settings are incompatible, characters may be corrupted.

17.1 Recommended Practices

• Prefer UTF-8 when exchanging files between compatible applications.
• Confirm the actual encoding of the source file.
• Use a suitable character set for the destination table and connection.
• Inspect non-English text after importing and exporting.
• Specify the file character set when necessary and supported by the chosen import workflow.

17.2 Example

LOAD DATA LOCAL INFILE 'C:/data/employees.csv'
INTO TABLE employees
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id, name, department, salary);

The character-set declaration must match the file's actual encoding. Declaring the wrong character set can still produce incorrect text.

18. Data Validation and Type Conversion

CSV values are stored as text, while MySQL columns may use numeric, date, decimal, or other data types. Invalid values should be identified before they reach the final table.

18.1 Common Data Problems

• Text such as unknown appears in a salary field.
• A date uses an unexpected format.
• A numeric field contains currency symbols.
• A value exceeds the destination column's length.
• A record violates a primary-key or unique-key constraint.

18.2 Create a Staging Table

A staging table can store raw incoming values before validation and conversion.

CREATE TABLE employees_staging (
    id_text VARCHAR(50),
    name_text VARCHAR(200),
    department_text VARCHAR(100),
    salary_text VARCHAR(50)
);

After loading the source data into this table, inspect missing required values:

SELECT *
FROM employees_staging
WHERE id_text IS NULL
   OR id_text = ''
   OR salary_text IS NULL
   OR salary_text = '';

Apply suitable validation and conversion rules before inserting approved records into the final table.

19. Handling Duplicate Records

Duplicate records may cause primary-key or unique-key errors during import. Decide how duplicates should be handled before loading the file.

19.1 Identify Duplicate IDs in a Staging Table

SELECT id_text, COUNT(*) AS occurrences
FROM employees_staging
GROUP BY id_text
HAVING COUNT(*) > 1;

19.2 Duplicate-Handling Options

• Reject duplicates: Correct the file before importing.
• Skip conflicting rows: Consider an appropriate IGNORE option only when skipping is acceptable.
• Replace conflicting rows: Use REPLACE only when overwriting existing records is intentional and safe.
• Deduplicate in staging: Identify the preferred record using documented business rules.

Do not use an error-handling option simply to make an import finish. Understand which rows may be skipped or replaced, and verify the result.

20. Importing and Exporting Large CSV Files

Large files require planning for execution time, disk space, indexes, constraints, and server workload.

20.1 Best Practices

• Use bulk-loading methods such as LOAD DATA when appropriate.
• Test the process with a small sample first.
• Confirm sufficient disk space and required permissions.
• Use a staging table when data requires extensive cleanup.
• Validate duplicate keys and data types.
• Review import warnings and affected-row counts.
• Schedule heavy operations carefully on busy systems.
• Test recovery procedures before important bulk updates.

20.2 Verify the Number of Records

SELECT COUNT(*) AS total_employees
FROM employees;

Compare the result with the expected source-record count, accounting for headers, intentionally excluded rows, and any documented rejection rules.

21. Common CSV Import and Export Errors

22. Security Best Practices

• Use database accounts with the minimum privileges required.
• Store input and output files in approved directories.
• Validate CSV files received from external sources.
• Export only the records and columns required for the task.
• Protect sensitive files with suitable access controls.
• Use secure database connections where appropriate.
• Enable LOCAL file loading only when necessary and with appropriate safeguards.
• Keep audit records for important data-transfer operations.
• Back up important data and test recovery procedures.
• Test bulk imports in a non-production environment when practical.

For more information, consult the official MySQL LOCAL INFILE security documentation.

23. Practical Project: Import and Export Employee Records

Project objective: Import employee records from a CSV file, verify the data, filter records, and export a department report.

Step 1: Create the Database

CREATE DATABASE employee_project;
USE employee_project;

Step 2: Create the Table

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(100),
    salary DECIMAL(10,2)
);

Step 3: Prepare the File

id,name,department,salary
101,Aarav,IT,45000.00
102,Meera,HR,40000.00
103,Rohan,Finance,52000.00
104,Ananya,Marketing,48000.00
105,Kabir,IT,55000.00

Step 4: Import the CSV

LOAD DATA INFILE '/var/lib/mysql-files/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, department, salary);

Use a valid server-side file path and the actual line-ending configuration. Alternatively, use LOCAL loading if it is supported, enabled, and permitted by your security policy.

Step 5: Verify the Imported Data

SELECT * FROM employees;

SELECT COUNT(*) AS total_records
FROM employees;

Step 6: Filter the IT Employees

SELECT id, name, department, salary
FROM employees
WHERE department = 'IT'
ORDER BY salary DESC;

Step 7: Export the Report

SELECT id, name, department, salary
FROM employees
WHERE department = 'IT'
ORDER BY salary DESC
INTO OUTFILE '/var/lib/mysql-files/it_employee_report.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Step 8: Validate the Results

• Confirm that the header row was skipped during import.
• Compare the imported row count with the source file.
• Check that values are in the correct columns.
• Inspect null values, numeric fields, and duplicate keys.
• Open the exported report and confirm the filtered records.
• Document any warnings and rejected rows.

24. Practice Exercises

1. Create a products table with product ID, name, category, and price.
2. Prepare a CSV file containing at least ten product records.
3. Import the file and verify the total number of records.
4. Import a CSV with a header row and ensure it is skipped.
5. Export all products into a CSV-compatible file.
6. Export only products priced above 1000.
7. Export selected columns rather than the entire table.
8. Load a test file with an invalid numeric value into a staging table.
9. Investigate how duplicate IDs affect the import.
10. Import a record whose quoted name contains a comma.

25. Interview Questions and Answers

Q1. What is CSV?

CSV stands for Comma-Separated Values. It is a text-based format for storing tabular data.

Q2. What is CSV importing?

It is the process of loading external CSV records into a MySQL table.

Q3. What is CSV exporting?

It is the process of extracting MySQL data and writing it to a CSV-compatible file.

Q4. Which statement is commonly used for bulk CSV imports?

LOAD DATA INFILE is commonly used for bulk imports from text files.

Q5. What is the difference between INFILE and LOCAL INFILE?

INFILE reads a server-accessible file, whereas LOCAL INFILE reads a client-side file and sends the contents to the server, subject to configuration.

Q6. How do you skip a header row?

Use IGNORE 1 LINES in a suitable LOAD DATA statement.

Q7. Which statement exports query results to a server-side file?

SELECT ... INTO OUTFILE exports query results to a file on the MySQL server host.

Q8. Does INTO OUTFILE automatically create headers?

No. Use a separate header-generating method or an export tool that supports column names.

Q9. What is secure_file_priv?

It is a MySQL system variable that can restrict server-side file import and export operations to an approved directory.

Q10. Why is column mapping important?

It ensures that incoming CSV fields are inserted into the correct destination columns.

Q11. How can you handle duplicate records?

Identify duplicates and decide whether to reject, skip, replace, or deduplicate them according to documented requirements.

Q12. Why do character-encoding problems occur?

They occur when the file's encoding and the database's character-set expectations do not match.

Q13. Why use a staging table?

A staging table allows raw records to be inspected, cleaned, deduplicated, and validated before insertion into the final table.

Q14. How do you verify a successful import?

Check the affected-row information, warnings, record count, sample values, data types, and constraints.

26. Quick Revision Checklist

• CSV stores tabular data in text form.
• Importing loads CSV records into MySQL.
• Exporting saves selected MySQL data to a file.
• LOAD DATA INFILE is a common bulk-import method.
• LOAD DATA LOCAL INFILE reads a client-side file when supported and enabled.
• IGNORE 1 LINES skips one header row.
• Column order, delimiters, quotation rules, and line endings must match the file.
• SELECT ... INTO OUTFILE writes a file on the server host.
• INTO OUTFILE does not automatically add column headings or overwrite an existing file.
• Validate row counts, missing values, duplicate keys, and data types.
• Use appropriate permissions and protect exported information.

27. Official Documentation and Learning Resources

• MySQL LOAD DATA Documentation
• MySQL SELECT INTO Documentation
• MySQL LOCAL INFILE Security Guidance
• MySQL Training Course at JustAcademy
• Register for a JustAcademy Course Demo

28. Conclusion

Importing and exporting CSV files is an essential MySQL skill for transferring data between databases, spreadsheets, reporting tools, and applications. The LOAD DATA statement supports efficient bulk imports, while SELECT ... INTO OUTFILE exports selected records to a server-side file. MySQL Workbench and client-side applications provide alternative approaches for suitable workflows.

Successful data transfer requires correct column mapping, delimiters, quotation rules, line endings, character encoding, and data validation. By checking imported records, handling duplicates deliberately, and following security practices, you can perform CSV import and export operations reliably.

Continue Learning: Explore the MySQL Training Course or register for a course demo.